﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.IO;
using NPOI;
using NPOI.Util;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using System.Web;
using YiYiTong.Rocket.Helper;

namespace YiYiTong.Rocket.Business
{
    public class ExportExcel
    {
        #region 读取Excel转换为DataTable

        static IWorkbook hssfworkbook;
        /// <summary>
        /// 读取Excel转换为DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public DataTable ImportExcelFile(string filePath)
        {
            //初始化信息
            #region
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch
            {
                return null;
            }
            #endregion

            DataTable dt = null;
            try
            {
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                dt = new DataTable();

                //一行最后一个方格的编号 即总的列数
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }

                while (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            catch
            {
                return null;
            }
            return dt;
        }

        public DataTable ImportExcelFile(Stream stream)
        {

            if (stream == null) return null;

            hssfworkbook = new HSSFWorkbook(stream);

            DataTable dt = null;
            try
            {
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                dt = new DataTable(sheet.SheetName);

                //一行最后一个方格的编号 即总的列数
                for (int j = 0; j < sheet.GetRow(0).Cells.Count; j++)
                {
                    dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
                }

                while (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;
                    if (row.RowNum == 0)
                        continue;

                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            catch
            {
                return null;
            }
            return dt;
        }
        #endregion

        /// <summary>
        /// 导出EXCEL
        /// </summary>
        /// <param name="dt">内容</param>
        /// <param name="lstTitle">标题</param>
        /// <param name="strOutPut"></param>
        /// <returns></returns>
        public string DtToExcel(DataTable dt, List<string> lstTitle, out string strOutPut)
        {
            strOutPut = "导出失败";
            var sbHtml = new StringBuilder();
            try
            {
                int colspan = 1;
                sbHtml.Append("<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />");
                sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
                if (lstTitle != null)
                {
                    sbHtml.Append("<tr>");

                    foreach (var item in lstTitle)
                    {
                        sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
                    }
                    sbHtml.Append("</tr>");
                    colspan = lstTitle.Count;
                }

                if (!dt.IsNotEmpty())
                {
                    sbHtml.Append("<tr>");
                    sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25' colspan='{0}'>暂无数据</td>", colspan);
                    sbHtml.Append("</tr>");
                }
                else
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sbHtml.Append("<tr>");
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] is StringNumber)
                                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;vnd.ms-excel.numberformat:@;'>{0}</td>", dt.Rows[i][j].ToString());
                            else
                                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", dt.Rows[i][j].ToString());
                        }
                        sbHtml.Append("</tr>");
                    }
                }
                sbHtml.Append("</table>");
                strOutPut = "导出成功";

                return sbHtml.ToString();
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.GetExcetionMessage());
            }
            return sbHtml.ToString();
        }

        private bool IsNumber(object obj)
        {
            return obj is decimal || obj is int || obj is long;
        }

        /// <summary>  
        /// 根据Excel列类型获取列的值  
        /// </summary>  
        /// <param name="cell">Excel列</param>  
        /// <returns></returns>  
        private string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return string.Empty;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue.ToString();
                case CellType.ERROR:
                    return cell.ErrorCellValue.ToString();
                case CellType.NUMERIC:
                case CellType.Unknown:
                default:

                    string unit = "";
                    if (cell.CellType == CellType.NUMERIC && DateUtil.IsCellDateFormatted(cell))
                    {
                        unit = cell.DateCellValue.ToString();
                    }
                    else
                    {
                        unit = cell.ToString();
                    }
                    return unit;

                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.FORMULA:
                    try
                    {
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString();
                        }

                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

    }

    public class PicturesInfo
    {
        public int MinRow { get; set; }
        public int MaxRow { get; set; }
        public int MinCol { get; set; }
        public int MaxCol { get; set; }
        public Byte[] PictureData { get; private set; }

        public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinCol = minCol;
            this.MaxCol = maxCol;
            this.PictureData = pictureData;
        }
    }

    public static class NpoiExtend
    {
        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
        {
            return sheet.GetAllPictureInfos(null, null, null, null);
        }

        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal = true)
        {
            if (sheet is HSSFSheet)
            {
                return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else
            {
                throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
            }
        }

        private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();

            var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
            if (null != shapeContainer)
            {
                var shapeList = shapeContainer.Children;
                foreach (var shape in shapeList)
                {
                    if (shape is HSSFPicture)
                    {
                        var picture = (HSSFPicture)shape;
                        var anchor = (HSSFClientAnchor)picture.Anchor;
                        if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                        {
                            picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                        }
                    }
                }
            }

            return picturesInfoList;
        }

        private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
            int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
        {
            int _rangeMinRow = rangeMinRow ?? pictureMinRow;
            int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
            int _rangeMinCol = rangeMinCol ?? pictureMinCol;
            int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;

            if (onlyInternal)
            {
                return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                        _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
            }
            else
            {
                return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
                (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
            }
        }

    }

}
